<?php
class Email_m extends CI_Model{
	
	public $user_name = '';
	function __construct(){//重载父类的数据库连接方法
		parent::__construct();
		$this->load->helper('string');
		$this->user_name = $this->session->userdata('user_name');
		$this->user_id = $this->session->userdata('user_id');
	}
	
	function getinboxlistdata($data){
		$this->db->from('email');
		$this->db->join('email_body', 'email_body.bodyid = email.bodyid');
		$this->db->where('email.userid', $this->user_id); 

		if(array_key_exists("title", $data))
			$this->db->like('email_body.title', $data['title']); 
		if(array_key_exists("sendname",$data))
			$this->db->where_in('email_body.sender',explode(",",reduce_multiples($data['sendname'],',',true)));
		if(array_key_exists("eids",$data))
			$this->db->where('email_body.sendertime >=', $data['eids']);
		if(array_key_exists("eide",$data)){
			$eide = date('Y-m-d',strtotime($this->input->post('eide')." + 1 day"));
			$this->db->where('email_body.sendertime <=', $eide);
		}
			
		$this->db->where_in('email.delete', array('0', '4'));
		
		//在order、group或limit前查询总数
        $db = clone($this->db);
        $total = $this->db->count_all_results();
		
		$this->db = $db;
		$this->db->order_by('email_body.'.$data['sort'], $data['order']); 
		$start=($data['page']-1)*$data['rows'];
		$this->db->limit($data['rows'],$start);
		$query = $this->db->get();
		//echo $this->db->last_query();//输出随后一次查询的语
		$data = $query->result_array();
		return array('data'=>$data, 'total'=>$total);
	}
	
	function setread($id){
		$data = array(
				   'read' => '1',
				   'readtime' => date("Y-m-d H:i:s",time())
				);
		$this->db->where_in('emailid', $id);
		$this->db->update('email', $data); 
		//echo $this->db->last_query();//输出随后一次查询的语
		return true;
	}
	
	/*删除邮件方法*/
	/*首先更新delete字段，0->1,4->2*/
	/*从emailid找到bodyid*/
	/*检查对应的bodyid字段是否全为2*/
	/*如果是则删除邮件主体和收件人信息*/
	function del_email($data){//收件箱删除邮件方法
		$this->db->trans_begin();//开始事务处理
		foreach($data['getid'] as $id){
			if($data['m'] == 'in'){
				$isSuccess = $this->db->query("update `yj_email` set `delete`= case `delete` when '0' then '1' when '4' then '2' end where `emailid` = '".$id."'");
				if($isSuccess){
					$query = $this->db->query("select * from `yj_email` where `bodyid` = (select `bodyid` from `yj_email` where `emailid` = '".$id."') and `delete` <> '2'");
					if($query->num_rows() == 0){
						$row = $this->db->query("select `bodyid` from `yj_email` where `emailid` = '".$id."'")->first_row();
						$tables = array('email', 'email_body');
						$this->db->where('bodyid', $row->bodyid);
						$this->db->delete($tables);	
					}
				}
			}else if($data['m'] == 'sent'){
				$isSuccess = $this->db->query("update `yj_email` set `delete`= case `delete` when '0' then '4' when '1' then '2' end where `bodyid` = '".$id."'");
				if($isSuccess){
					$query = $this->db->query("select * from `yj_email` where `bodyid` = '".$id."' and `delete` <> '2'");
					if($query->num_rows() == 0){
						$tables = array('email', 'email_body');
						$this->db->where('bodyid', $id);
						$this->db->delete($tables);	
					}
				}
			}
			
		}
		if($this->db->trans_status() === FALSE){
			$this->db->trans_rollback();//事件回滚	
			return false;		
		}else{
			$this->db->trans_commit();//提交成功
			return true;
		}//事务结束
	}
	function contentbox($m,$id){
		if($m == 'in'){//$m为收件箱
			$this->setread($id);//将该条邮件设置为已读
			/*
			$sql = "SELECT * FROM yj_email LEFT JOIN yj_email_body ON yj_email.bodyid = yj_email_body.bodyid WHERE ";			
			if($f !== 0){
				if($f == 'u'){//上一条
					$sql .= "yj_email.emailid = (select yj_email.emailid from yj_email where yj_email.emailid < ? and yj_email.delete in (0,4) and yj_email.addressee = ? order by yj_email.emailid  desc limit 1) ";
				}else{
					$sql .= "yj_email.emailid = (select yj_email.emailid from yj_email where yj_email.emailid > ? and yj_email.delete in (0,4) and yj_email.addressee = ? order by yj_email.emailid asc limit 1) ";
				}
			}else{
				$sql .= "yj_email.emailid = ? and yj_email.delete in (0,4) and yj_email.addressee = ? limit 1";	
			}
			*/
			$sql = "SELECT * FROM yj_email LEFT JOIN yj_email_body ON yj_email.bodyid = yj_email_body.bodyid WHERE yj_email.emailid = ? and yj_email.delete in (0,4) and yj_email.addressee = ? limit 1";		
			$query = $this->db->query($sql, array($id,$this->user_name));
			return $query->result();
		}else if($m == 'sent'){//$m为发件箱
			/*
			$sql = "SELECT * FROM yj_email_body WHERE ";
			if($f !== 0){
				if($f == 'u'){//上一条
					$sql .= "bodyid = (select `bodyid` from `yj_email` where `bodyid` < ? and `delete` in (0,1) order by `bodyid` desc limit 1) and `sender` = ? limit 1";
				}else if($f == 'n'){
					$sql .= "bodyid = (select `bodyid` from `yj_email` where `bodyid` > ? and `delete` in (0,1) order by `bodyid` asc limit 1) and `sender` = ? limit 1";
				}
			}else{
				$sql .= "`bodyid` = ? limit 1";	
			}
			*/
			$sql = "SELECT * FROM yj_email_body WHERE `bodyid` = ? limit 1";
			$query = $this->db->query($sql, array($id));
			//echo $this->db->last_query();//输出随后一次查询的语
			return $query->result();
		}
	}
	
/*--已发送---------------------------------------------*/
	function getsentboxlistdata($data){
		$this->db->from('email');
		$this->db->join('email_body', 'email_body.bodyid = email.bodyid','right');
		$this->db->where('email_body.sender', $this->user_name); 

		if(array_key_exists("title",$data))
			$this->db->like('email_body.title', $data['title']); 
		if(array_key_exists("sendname",$data))
			$this->db->where_in('email.addressee',explode(",",reduce_multiples($data['sendname'],',',true)));
		if(array_key_exists("eids",$data))
			$this->db->where('email_body.sendertime >=', $data['eids']);
		if(array_key_exists("eide",$data)){
			$eide = date('Y-m-d',strtotime($this->input->post('eide')." + 1 day"));
			$this->db->where('email_body.sendertime <=', $eide);
		}
		
		$this->db->where_in('email.delete', array('0', '1'));
		
		//在order、group或limit前查询总数
        $db = clone($this->db);
        $total = $this->db->count_all_results();
		
		$this->db = $db;		
		$this->db->group_by('email_body.bodyid'); 
		$this->db->order_by('email_body.'.$data['sort'], $data['order']); 
		$start=($data['page']-1)*$data['rows'];
		$this->db->limit($data['rows'],$start);
		$query = $this->db->get();
		//echo $this->db->last_query();//输出随后一次查询的语
		$data = $query->result_array();
		return array('data'=>$data, 'total'=>$total);
	}

	/*发件箱删除邮件方法*/
	/*首先更新delete字段，0->1,4->2*/
	/*从emailid找到bodyid*/
	/*检查对应的bodyid字段是否全为2*/
	/*如果是则删除邮件主体和收件人信息*/
	/*
	function sentboxdel($id){//发件箱删除邮件方法
		$this->db->trans_begin();//开始事务处理
		foreach($id as $bodyid){
			$isSuccess = $this->db->query("update `yj_email` set `delete`= case `delete` when '0' then '4' when '1' then '2' end where `bodyid` = '".$bodyid."'");
			if($isSuccess){
				$query = $this->db->query("select * from `yj_email` where `bodyid` = '".$bodyid."' and `delete` <> '2'");
				if($query->num_rows() == 0){
					$tables = array('email', 'email_body');
					$this->db->where('bodyid', $bodyid);
					$this->db->delete($tables);	
				}
			}
		}
		if($this->db->trans_status() === FALSE){
			$this->db->trans_rollback();//事件回滚	
			return false;		
		}else{
			$this->db->trans_commit();//提交成功
			return true;
		}//事务结束
	}
	*/
	
	/*发件箱删除用户邮件方法*/
	/*首先更新delete字段，0->1,4->2*/
	/*从emailid找到bodyid*/
	/*检查对应的bodyid字段是否全为2*/
	/*如果是则删除邮件主体和收件人信息*/
	function sentuserboxdel($id){//发件箱删除用户邮件方法
		//$this->db->trans_begin();//开始事务处理
		$query = $this->db->query("select * from `yj_email` where `bodyid` = (select `bodyid` from `yj_email` where `emailid` = '".$id[0]." limit 1')");
		if($query->num_rows() == count($id)){
			//如果提交的ID条数等于该条邮件的收件人条数，则直接删除邮件主体和收件人列表
			$tables = array('email', 'email_body');
			$this->db->where('bodyid', $query->first_row()->bodyid);
			$this->db->delete($tables);
		}else{
			//1.找出对应的addressee2字符串
			$query = $this->db->query("select * from `yj_email_body` where `bodyid` = (select `bodyid` from `yj_email` where `emailid` = '".$id[0]." limit 1')");
			$addressee2 = $query->first_row()->addressee2;
			//2.取出提交的收件人名称，循环替换后更新addressee2字段
			$this->db->where_in('emailid', $id);
			$query1 = $this->db->get('email');
			foreach ($query1->result_array() as $row){
				$addressee2 = str_replace($row['addressee'].',', "", $addressee2);
			}
			$this->db->update('email_body', array('addressee2'=>$addressee2), array('bodyid' => $query->first_row()->bodyid));
			//3.然后执行删除对应的邮件记录，这里是彻底删除某条收件人信息，不是单纯的改变邮件状态
			$this->db->where_in('emailid', $id);
			$this->db->delete('email');	
		}
		return true;
		//echo $this->db->last_query();//输出随后一次查询的语
		/*
		$this->db->where_in('emailid', $id);
		$this->db->delete('email'); 
		
		foreach($id as $emailid){
			$isSuccess = $this->db->query("DELETE FROM `yj_email` WHERE `emailid` = '".$emailid."'");
			if($isSuccess){
				$query = $this->db->query("select * from `yj_email` where `bodyid` = '".$bodyid."' and `delete` <> '2'");
				if($query->num_rows() == 0){
					$tables = array('email', 'email_body');
					$this->db->where('bodyid', $bodyid);
					$this->db->delete($tables);	
				}
			}
		}
		if($this->db->trans_status() === FALSE){
			$this->db->trans_rollback();//事件回滚	
			return false;		
		}else{
			$this->db->trans_commit();//提交成功
			return true;
		}//事务结束
		*/
	}

	function emailaddpost($data){//添加新邮件数据到数据库
	
		//判断并合并收件人和抄送人
		//if(@$data['p_cc_id'] != ''){
			//$cc = $data['p_cc_user'];
			$uidarr = explode(",",reduce_multiples($data['p_sj_id'].$data['p_cc_id'],',',true));
			$uidarr = array_merge(array_unique($uidarr));//array_unique移除重复的收件人ID，array_merge对下标重新排序
			
			$user_arr = explode(",",reduce_multiples($data['p_sj_user'].$data['p_cc_user'],',',true));
			$user_arr = array_merge(array_unique($user_arr));//array_unique移除重复的收件人USER，array_merge对下标重新排序
		//}else{
			//$cc = '';
		//	$uidarr = explode(",",substr($data['p_sj_id'],0,-1));
		//}
		
		//是否重要
		$important = (@$data['p_important'] == 'true')?1:0;
		
		//是否短信提醒
		$smsalerts = (@$data['p_sms'] == 'true')?1:0;
		//开始事务处理
		$this->db->trans_begin();
			$array = array(//邮件主体数据
				'bodyid' => '', 
				'senderid' => $this->user_id, 
				'sender' => $this->user_name, 
				'addressee2' => $data['p_sj_user'],
				'cc' => $data['p_cc_user'], 
				'title'=> $data['p_title'],
				'content'=> $data['p_content'],
				'sendertime'=> date("Y-m-d H:i:s",time()),
				'attachment_id'=> $data['p_att_id'],
				'attachment_name'=> $data['p_att_name'],
				'smsalerts'=> $smsalerts,
				'important'=> $important,				
				'size'=> $data['p_att_size']
			);
			$this->db->set($array);
			$this->db->insert('email_body'); //插入邮件主体
			$bodyid = $this->db->insert_id();

			if($smsalerts == 1){
				//邮件短信提醒数据主体插入到数据库			
				$smsarr = array(
					'bodyid' => '' ,
					'sender' => $this->user_name,
					'smstype' => '2',
					'alerttype' => '0',
					'content' => '请查收我的邮件！ 主题：'.$data['p_title'],
					'sendtime' => date("Y-m-d H:i:s",time()),
					'remindurl' => 'email/inbox/'.$bodyid
				 ); 
				$this->db->insert('sms_body', $smsarr); //插入短信提醒主体数据
				$bodyid2 = $this->db->insert_id();
			}
			
			//$addressee = explode(",",substr($data['p_sj_user'],0,-1));
			for($i = 0; $i<count($uidarr);$i++){
				$arr[0][] = array(//邮件收件人数据
					'emailid' => '',
					'userid' => $uidarr[$i],
					'addressee' => $user_arr[$i],
					'read' => '0',
					'readtime' => '',
					'delete' => '0',
					'bodyid' => $bodyid
				);
				if($smsalerts == 1){
					$arr[1][] = array(//短信收信人数据
						'smsid' => '',
						'to_id' => $uidarr[$i],
						'to_name' => $user_arr[$i],
						'sms_read' => '0',
						'readtime' => '',
						'sms_delete' => '0',
						'bodyid' => $bodyid2
					);
					$arr[2][] = array(//用于检索的数据表数据
						'id' => '',
						'u_id' => $uidarr[$i],
						'u_name' => $user_arr[$i],
						'add_date' => time(),
						'm_id' => '2_'.$bodyid2
					);
				}
			}
			$this->db->insert_batch('email',$arr[0]); //插入接收邮件数组
			if($smsalerts == 1){
				$this->db->insert_batch('sms',$arr[1]); //插入接收邮件数组
				$this->db->insert_batch('sms_check',$arr[2]); //插入接收邮件数组
			}
		
		if($this->db->trans_status() === FALSE){
			$this->db->trans_rollback();//事件回滚	
			return false;		
		}else{
			$this->db->trans_commit();//提交成功
			return true;
		}//事务结束
	}
	function readStatusdata($bodyid){//查看收件人详细状态
		$this->db->where('bodyid', $bodyid);	
		$names = array('0', '1');
 		$this->db->where_in('delete', $names);
		$this->db->order_by("emailid", "desc"); 
		$query=$this->db->get('email');
		return $query->result();
	}
	
}
?>